package com.weishao.example;

import java.util.List;
import java.util.ArrayList;
import org.apache.commons.dbcp2.BasicDataSource;
import com.weishao.dbwriter.DatabaseWriterFactory;
import com.weishao.dbwriter.IDatabaseWriter;

/**
 * 输入写入三种数据库的DEMO示例
 * 
 * @author tang
 *
 */
public class DbwriterExampleApplication {
	
	public static BasicDataSource getPostgresqlDataSource() {
		/**
		 *  示例的表结构如下：
			CREATE TABLE "private"."yktxt_yktjyxx_V_ZJK_YKT_JYXX" (
				"XGH" varchar(80) ,
				"XM" char(120) ,
				"LSH" int8,
				"ZH" int8,
				"JYLX" varchar(160) ,
				"SH" varchar(160) ,
				"ZDDM" varchar(160) ,
				"ZDMC" char(160) ,
				"JYE" varchar(56) ,
				"YE" varchar(56) ,
				"JYSJ" timestamp(6),
				"CJSJ" varchar(76) ,
				"JZSJ" char(32)
			)WITH (OIDS=FALSE);
		 * 
		 */
		// 基于DBCP2连接池构造数据源
		//说明：
		//  （1）对于使用GP的COPY写入时，当前只支持DPCP2的数据库连接池，并使用postgresql的驱动包；
		//  （2）对于使用GP的INSERT写入时，对数据源的使用的数据库连接池无限制，但需要使用greenplum的驱动包
		BasicDataSource dataSource = new BasicDataSource();
		dataSource.setDriverClassName("org.postgresql.Driver");
		dataSource.setUrl("jdbc:postgresql://172.17.207.210:5432/tangyibo");
		dataSource.setUsername("tangyibo");
		dataSource.setPassword("tangyibo");
		dataSource.setInitialSize(5);
		dataSource.setMaxIdle(5);
		dataSource.setMinIdle(2);
		return dataSource;
	}
	
	public static BasicDataSource getGreenplumDataSource() {
		/**
		 *  示例的表结构如下：
			CREATE TABLE "private"."yktxt_yktjyxx_V_ZJK_YKT_JYXX" (
				"XGH" varchar(80) ,
				"XM" char(120) ,
				"LSH" int8,
				"ZH" int8,
				"JYLX" varchar(160) ,
				"SH" varchar(160) ,
				"ZDDM" varchar(160) ,
				"ZDMC" char(160) ,
				"JYE" varchar(56) ,
				"YE" varchar(56) ,
				"JYSJ" timestamp(6),
				"CJSJ" varchar(76) ,
				"JZSJ" char(32)
			)WITH (OIDS=FALSE);
		 * 
		 */
		// 基于DBCP2连接池构造数据源
		BasicDataSource dataSource = new BasicDataSource();
		dataSource.setDriverClassName("com.pivotal.jdbc.GreenplumDriver");
		dataSource.setUrl("jdbc:pivotal:greenplum://172.17.207.151:5432;DatabaseName=study");
		dataSource.setUsername("study");
		dataSource.setPassword("123456");
		dataSource.setInitialSize(5);
		dataSource.setMaxIdle(5);
		dataSource.setMinIdle(2);
		return dataSource;
	}
	
	public static BasicDataSource getOracleDataSource() {
		/**
		 *  示例的表结构如下：
			CREATE TABLE "YI_BO"."yktxt_yktjyxx_V_ZJK_YKT_JYXX" (
				"XGH" varchar(80) ,
				"XM" char(120) ,
				"LSH" NUMBER,
				"ZH" NUMBER,
				"JYLX" varchar(160) ,
				"SH" varchar(160) ,
				"ZDDM" varchar(160) ,
				"ZDMC" char(160) ,
				"JYE" varchar(56) ,
				"YE" varchar(56) ,
				"JYSJ" date,
				"CJSJ" varchar(76) ,
				"JZSJ" char(32)
			)
		 * 
		 */
		// 基于DBCP2连接池构造数据源
		BasicDataSource dataSource = new BasicDataSource();
		dataSource.setDriverClassName("oracle.jdbc.driver.OracleDriver");
		dataSource.setUrl("jdbc:oracle:thin:@172.17.207.252:1521:ORCL");
		dataSource.setUsername("yi_bo");
		dataSource.setPassword("tangyibo");
		dataSource.setInitialSize(5);
		dataSource.setMaxIdle(5);
		dataSource.setMinIdle(2);
		return dataSource;
	}
	
	public static BasicDataSource getMySQLDataSource() {
		/**
		 *  示例的表结构如下：
			CREATE TABLE `test`.`yktxt_yktjyxx_V_ZJK_YKT_JYXX` (
				`XGH` VARCHAR (80),
				`XM` CHAR (120),
				`LSH` INT,
				`ZH` INT,
				`JYLX` VARCHAR (160),
				`SH` VARCHAR (160),
				`ZDDM` VARCHAR (160),
				`ZDMC` CHAR (160),
				`JYE` VARCHAR (56),
				`YE` VARCHAR (56),
				`JYSJ` TIMESTAMP (6),
				`CJSJ` VARCHAR (76),
				`JZSJ` CHAR (32)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8;
		 * 
		 */
		
		// 基于DBCP2连接池构造数据源
		BasicDataSource dataSource = new BasicDataSource();
		dataSource.setDriverClassName("com.mysql.jdbc.Driver");
		dataSource.setUrl("jdbc:mysql://172.17.207.210:3306/test?autoReconnect=true&useUnicode=true&characterEncoding=utf-8&useSSL=false");
		dataSource.setUsername("tangyibo");
		dataSource.setPassword("tangyibo");
		dataSource.setInitialSize(5);
		dataSource.setMaxIdle(5);
		dataSource.setMinIdle(2);
		
		return dataSource;
	}

	@SuppressWarnings("serial")
	public static void main(String[] args) {
		String database="postgresql";
		
		BasicDataSource dataSource=null;
		String schemaName=null;
		IDatabaseWriter writer=null;
		
		// 构造数据写入器，支持MySQL、Oracle、PostgreSQL/Greenplum三种类型，
		if(database.equals("mysql")) {
			dataSource=DbwriterExampleApplication.getMySQLDataSource();
			writer = DatabaseWriterFactory.createDatabaseWriter(dataSource);
			schemaName="test";
		}else if(database.equals("oracle")) {
			dataSource=DbwriterExampleApplication.getOracleDataSource();
			writer = DatabaseWriterFactory.createDatabaseWriter(dataSource);
			schemaName="YI_BO";
		}else if(database.equals("postgresql")) {
			dataSource=DbwriterExampleApplication.getPostgresqlDataSource();
			writer = DatabaseWriterFactory.createDatabaseWriter(dataSource);
			schemaName="private";
		}else if(database.equals("greenplum")) {
			dataSource=DbwriterExampleApplication.getPostgresqlDataSource();
			writer = DatabaseWriterFactory.createDatabaseWriter(dataSource,true);
			schemaName="private";
		}else {
			System.out.println("测试的数据库类型错误！");
			return;
		}
			
		String tableName = "yktxt_yktjyxx_V_ZJK_YKT_JYXX";
		String[] fieldColumn = { "LSH", "CJSJ", "JYSJ", "JZSJ", "ZDMC", "YE", "ZH", "XM", "SH", "JYE", "XGH", "JYLX",	"ZDDM" };
		Object[] itemValue = { "4388660", "2019-01-01 09:52:51", null, "20190101", "第三方对接子系统", "4.10",
				"38062", "吴孟瑶", "南校宿舍开水", "-0.07", "2016212572", "持卡人消费", "45" };

		//做预处理准备
		writer.prepareWrite(schemaName, tableName);

		// 模拟写入的数据
		List<String> fieldNames = new ArrayList<String>() {
			{
				for (String one : fieldColumn) {
					add(one);
				}
			}
		};

		// 执行实际的数据写入
		long begin = System.currentTimeMillis();
		int maxCount = 10000;
		boolean batch = true;
		try {
			if (!batch) {
				// 单条写入方式
				for (int i = 0; i < maxCount; ++i) {
					List<Object[]> recordValues = new ArrayList<Object[]>() {
						{
							add(itemValue);
						}
					};
					long ret = writer.write(fieldNames, recordValues);
					System.out.println("affect_row_count=" + ret);
				}
			} else {
				// 批量写入方式
				List<Object[]> recordValues = new ArrayList<Object[]>() {
					{
						for (int i = 0; i < maxCount; ++i) {
							add(itemValue);
						}
					}
				};
				long ret = writer.write(fieldNames, recordValues);
				System.out.println("affect_row_count=" + ret);
			}
		} finally {
			long end = System.currentTimeMillis();
			System.out.println("total elipse = " + (end - begin) + " ms");
		}
	}

}
